import scrapy
import xlwt,xlrd
import time
import pandas as pd
from datetime import datetime
# scrapy runspider myspider.py  运行指令



class WriteExcel():
    def __init__(self,sheet_name,excel_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        self.ws = self.wb.add_sheet(sheet_name)
        self.excel_name = excel_name

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_{}_{}.xls'.format(self.excel_name,current_time))


class MySpider(scrapy.Spider):
    name = 'myspider'
    start_urls = ['file:///Users/v_baiguanghui/Downloads/%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90%E5%A4%8D%E6%B5%8B.html']
    def parse(self, response):
        links = response.css('body > div > details').getall()
        print('长度', len(links))

        writeObj = WriteExcel('Sheet', '数据分析')
        writeObj.write_excel_body(0, 0,'指令')
        writeObj.write_excel_body(0, 1,'asr文案输入')
        writeObj.write_excel_body(0, 2,'ws接收asr时刻')
        writeObj.write_excel_body(0, 3,'asr识别完时刻')
        writeObj.write_excel_body(0, 4,'发起asr接口时刻')
        writeObj.write_excel_body(0, 5, '请求意图参数')
        writeObj.write_excel_body(0, 6, '意图识别返回')
        writeObj.write_excel_body(0, 7, '请求数据分析参数')
        writeObj.write_excel_body(0, 8, '请求生成SQL')
        writeObj.write_excel_body(0, 9, '生成SQL返回')
        writeObj.write_excel_body(0, 10, '执行的SQL语句')
        writeObj.write_excel_body(0, 11, '请求图表分析')
        writeObj.write_excel_body(0, 12, '图表分析返回')
        writeObj.write_excel_body(0, 13, '开始响应')
        writeObj.write_excel_body(0, 14,'结束本次流程时刻')
        writeObj.write_excel_body(0, 15, 'asrToFrontCost')
        writeObj.write_excel_body(0, 16, 'FrontToReqCost')
        writeObj.write_excel_body(0, 17, 'intentCost')
        writeObj.write_excel_body(0, 18, 'genSqlCost')
        writeObj.write_excel_body(0, 19, 'execSqlCost')
        writeObj.write_excel_body(0, 20, 'genChartCost')
        writeObj.write_excel_body(0, 21, 'totalCost')
        writeObj.write_excel_body(0, 22,'中间件返回内容')
        writeObj.write_excel_body(0, 23,'数据分析内容返回')



        with open('links.html', 'a') as file:
            # for link in links:
            #     file.write(link + '\n')
            for count in range(1,len(links)+1):
                divNum = response.css('body > div > details:nth-child({}) > dl > div'.format(count)).getall()

                # 是否为标准输出：7为标准输出的行数
                if len(divNum) == 7:
                    cmd = response.css('body > div > details:nth-child({}) > summary::text'.format(count)).get().split('：')[1]
                    cmd1 = response.css('body > div > details:nth-child({}) > summary::text'.format(count)).get()
                    if cmd1 == "指令：" or cmd1.startswith("指令：{"):    # 语音输入为空
                        continue
                    else:
                        midWareReturn = response.css(
                            'body > div > details:nth-child({}) > dl > div:nth-child(4) > dd > pre::text'.format(
                                count)).get()
                        index_type = midWareReturn.find('type')
                        index_typeEnd = midWareReturn.find('"', index_type + 8)
                        if midWareReturn[index_type + 8:index_typeEnd] == '数据分析':
                            notNull = response.css('body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'.format(count)).get()
                            print(notNull)
                            if notNull:
                                asrTextInputTime = response.css('body > div > details:nth-child({}) > dl > div:nth-child(1) > dt::text'.format(count)).get().split('- ')[1]
                                wsRevAsrTime = response.css('body > div > details:nth-child({}) > dl > div:nth-child(2) > dt::text'.format(count)).get().split('- ')[1]
                                asrIntentTime = response.css('body > div > details:nth-child({}) > dl > div:nth-child(2) > dd > pre::text'.format(count)).get().split('"time": ')[1][0:13]
                                asrStartReqTime = response.css('body > div > details:nth-child({}) > dl > div:nth-child(3) > dt::text'.format(count)).get().split('- ')[1]
                                midWareReturn = response.css('body > div > details:nth-child({}) > dl > div:nth-child(4) > dd > pre::text'.format(count)).get()

                                reqAnalysisTime = response.css('body > div > details:nth-child({}) > dl > div:nth-child(5) > dt::text'.format(count)).get().split('- ')[1]
                                resAnalysisTime = response.css('body > div > details:nth-child({}) > dl > div:nth-child(6) > dt::text'.format(count)).get().split('- ')[1]
                                resAnalysisContent = response.css('body > div > details:nth-child({}) > dl > div:nth-child(6) > dd > pre::text'.format(count)).get()
                                endTime = response.css('body > div > details:nth-child({}) > dl > div:nth-child(7) > dt::text'.format(count)).get().split('- ')[1]
                                # print(cmd,endTime)
                                # file.write(cmd + '\n' + asrTextInputTime + '\n'+wsRevAsrTime + '\n' + asrIntentTime +'\n' + asrStartReqTime+ '\n' +midWareReturn+'\n'+endTime+'\n')
                                # file.write(cmd+'\n')

                                index_reqPars = midWareReturn.find('请求意图参数",')
                                if index_reqPars != -1:
                                    index_reqPars_time = midWareReturn.find('timestamp', index_reqPars)
                                    reqIntent_time = midWareReturn[index_reqPars_time+13:index_reqPars_time+26:1]
                                    print('请求意图参数：', reqIntent_time)
                                else:
                                    print('请求意图参数：空')

                                index_resInent = midWareReturn.find('意图识别返回",')
                                if index_resInent != -1:
                                    index_resInent_time = midWareReturn.find('timestamp', index_resInent)
                                    resIntent_time = midWareReturn[index_resInent_time + 13:index_resInent_time + 26:1]
                                    print('意图识别返回：', resIntent_time)
                                else:
                                    print('意图识别返回：空')

                                index_reqAnalyPars = resAnalysisContent.find('请求数据分析参数",')
                                if index_reqAnalyPars != -1:
                                    index_reqAnalyPars_time = resAnalysisContent.find('timestamp', index_reqAnalyPars)
                                    reqAnalyPars_time = resAnalysisContent[index_reqAnalyPars_time + 13:index_reqAnalyPars_time + 26:1]
                                    print('请求数据分析参数：', reqAnalyPars_time)
                                else:
                                    print('请求数据分析参数：空')

                                index_reqGenSql = resAnalysisContent.find('请求生成SQL",')
                                if index_reqGenSql != -1:
                                    index_reqGenSql_time = resAnalysisContent.find('timestamp', index_reqGenSql)
                                    reqGenSql_time = resAnalysisContent[index_reqGenSql_time + 13:index_reqGenSql_time + 26:1]
                                    print('请求生成sql：', reqGenSql_time)
                                else:
                                    print('请求生成sql：空')

                                index_resSql = resAnalysisContent.find('生成SQL返回",')
                                if index_resSql != -1:
                                    index_resSql_time = resAnalysisContent.find('timestamp', index_resSql)
                                    resSql_time = resAnalysisContent[index_resSql_time + 13:index_resSql_time + 26:1]
                                    print('生成SQL返回：', resSql_time)
                                else:
                                    print('生成SQL返回：空')

                                index_execSql = resAnalysisContent.find('执行的SQL语句",')
                                if index_execSql != -1:
                                    index_execSql_time = resAnalysisContent.find('timestamp', index_execSql)
                                    execSql_time = resAnalysisContent[index_execSql_time + 13:index_execSql_time + 26:1]
                                    print('执行的SQL语句：', execSql_time)
                                else:
                                    print('执行的SQL语句：空')

                                index_execSql = resAnalysisContent.find('SQL执行结果返回条数",')
                                if index_execSql != -1:
                                    index_execSql_time = resAnalysisContent.find('timestamp', index_execSql)
                                    reqChart_time = resAnalysisContent[index_execSql_time + 13:index_execSql_time + 26:1]
                                    resChart_time = reqChart_time       # 如果sql执行结果返回0，将生成图表时间配置为0
                                    print('SQL执行结果返回条数', reqChart_time)

                                index_reqChart = resAnalysisContent.find('请求图表分析",')
                                if index_reqChart != -1:
                                    index_reqChart_time = resAnalysisContent.find('timestamp', index_reqChart)
                                    reqChart_time = resAnalysisContent[index_reqChart_time + 13:index_reqChart_time + 26:1]
                                    print('请求图表分析：', reqChart_time)
                                else:
                                    print('请求图表分析：空')

                                index_resChart = resAnalysisContent.find('图表分析返回",')
                                if index_resChart != -1:
                                    index_resChart_time = resAnalysisContent.find('timestamp', index_resChart)
                                    resChart_time = resAnalysisContent[index_resChart_time + 13:index_resChart_time + 26:1]
                                    print('图表分析返回：', resChart_time)
                                else:
                                    print('图表分析返回：空')

                                index_resTime = resAnalysisContent.find('开始响应",')
                                if index_resTime != -1:
                                    index_res_time = resAnalysisContent.find('timestamp', index_resTime)
                                    res_time = resAnalysisContent[index_res_time + 13:index_res_time + 26:1]
                                    print('开始响应：', res_time)
                                else:
                                    print('开始响应：空')

                                dt = datetime.strptime(asrTextInputTime, "%Y/%m/%d %H:%M:%S")
                                timestamp = int(dt.timestamp()) * 1000

                                asrToFrontCost = int(timestamp) - int(asrIntentTime)
                                FrontToReqCost = int(reqIntent_time) - int(timestamp)
                                intentCost = int(resIntent_time) - int(reqIntent_time)
                                genSqlCost = int(resSql_time) - int(reqGenSql_time)
                                execSqlCost = int(reqChart_time) - int(execSql_time)
                                genChartCost = int(resChart_time) - int(reqChart_time)
                                totalCost = int(res_time) - int(asrIntentTime)

                                writeObj.write_excel_body(count, 0, cmd)
                                writeObj.write_excel_body(count, 1, asrTextInputTime)
                                writeObj.write_excel_body(count, 2, wsRevAsrTime)
                                writeObj.write_excel_body(count, 3, asrIntentTime)
                                writeObj.write_excel_body(count, 4, asrStartReqTime)
                                writeObj.write_excel_body(count, 5, reqIntent_time)
                                writeObj.write_excel_body(count, 6, resIntent_time)
                                writeObj.write_excel_body(count, 7, reqAnalyPars_time)
                                writeObj.write_excel_body(count, 8, reqGenSql_time)
                                writeObj.write_excel_body(count, 9, resSql_time)
                                writeObj.write_excel_body(count, 10, execSql_time)
                                writeObj.write_excel_body(count, 11, reqChart_time)
                                writeObj.write_excel_body(count, 12, resChart_time)
                                writeObj.write_excel_body(count, 13, res_time)
                                writeObj.write_excel_body(count, 14, endTime)
                                writeObj.write_excel_body(count, 15, asrToFrontCost)
                                writeObj.write_excel_body(count, 16, FrontToReqCost)
                                writeObj.write_excel_body(count, 17, intentCost)
                                writeObj.write_excel_body(count, 18, genSqlCost)
                                writeObj.write_excel_body(count, 19, execSqlCost)
                                writeObj.write_excel_body(count, 20, genChartCost)
                                writeObj.write_excel_body(count, 21, totalCost)
                                writeObj.write_excel_body(count, 22, midWareReturn)
                                writeObj.write_excel_body(count, 23, resAnalysisContent[0:32766])
                else:
                    pass
        writeObj.save_excel()

if __name__ == '__main__':
    import sys

    if len(sys.argv) > 1:
        start_url = sys.argv[1]
    else:
        start_url = 'file:///Users/v_baiguanghui/Downloads/aaa.html'

    spider = MySpider()
    spider.start_urls = [start_url]
    spider.run()

